package com.hava.transition;

import org.apache.commons.dbcp2.BasicDataSource;

import java.sql.*;

/**
 * Created by yanfa on 2016/10/8.
 */
public class AccountDAO {

    public static BasicDataSource basicDataSource = null;

    public final static String JDBC_DRIVER = "com.mysql.jdbc.Driver";
    public final static String DB_URL = "jdbc:mysql://192.168.1.200/test";
    public final static String USER = "root";
    public final static String PASSWORD = "dVHJtG0T:pf*";

    public void init()
    {
        basicDataSource = new BasicDataSource();
        basicDataSource.setUrl(DB_URL);
        basicDataSource.setDriverClassName(JDBC_DRIVER);
        basicDataSource.setUsername(USER);
        basicDataSource.setPassword(PASSWORD);
    }

    public void transferAccount() throws ClassNotFoundException {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        Savepoint savepoint = null;
        try {
            connection = basicDataSource.getConnection();
            //开启事务
            connection.setAutoCommit(false);
            String sql = "UPDATE student_transaction SET account = ? WHERE name = ?";

            preparedStatement = connection.prepareStatement(sql);

            preparedStatement.setInt(1,0);
            preparedStatement.setString(2,"ZhangSan");
            preparedStatement.execute();

            //保存检查点
            savepoint = connection.setSavepoint();

            preparedStatement.setInt(1,100);
            preparedStatement.setString(2,"LiSi");
            preparedStatement.execute();

            throw new SQLException();
//            //提交事务
//            connection.commit();

        } catch (SQLException e) {
            // ignore
            System.out.println("[SQLException]:" + e.toString());
            //如果发生异常则回滚事务
            if(connection != null)
                try {
                    //发生异常回滚到之前的断点
                    connection.rollback(savepoint);
                    //执行之后的业务逻辑
                    preparedStatement.setInt(1,100);
                    preparedStatement.setString(2,"ZhaoWu");
                    preparedStatement.execute();
                    connection.commit();

                } catch (SQLException e1) {
                    //如果出现异常直接回滚到事务之前
                    e1.printStackTrace();
                }
        } finally {
                if (preparedStatement != null)
                {
                    try {
                        preparedStatement.close();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                }

            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}
